//cd C:\Users\gu_w\Documents
clear all 
set more off
log using DataLog_Q_job_upto2018, replace

qui use GU04_14_Q_j_ur_all_2018, replace
//=========to make sure only real n3 shows up==========
sort n3
save masterfile, replace

qui use naics2002_n3, clear 
sort n3
save usefile, replace

qui use masterfile, clear
merge m:1 n3 using usefile
sort _merge
drop if _merge<3
drop _merge

//n3, n2, n1
drop if missing(n3)
qui replace n2=int(n3/10)
qui replace n1=int(n3/100)
tabulate n1
tabulate n2
tabulate n3

//========to make sure only real states shows up===========
sort j_bls_state_code2
save masterfile, replace

qui use bls_states, clear
sort j_bls_state_code2
save usefile, replace

qui use masterfile, clear
merge m:1 j_bls_state_code2 using usefile
sort _merge
drop if _merge<3
drop _merge

tabulate j_bls_state_code2

//========to make sure only real mog shows up===========
sort j_mognum
save masterfile, replace

qui use mognum_2017, clear 
sort j_mognum
save usefile, replace

qui use masterfile, clear
merge m:1 j_mognum using usefile
sort _merge
drop if _merge<3
drop _merge

tabulate j_mognum

//Append
append using GU15_18_Q_j

save GU04_18_Q_j_all, replace

///macro data
sort yrq
save masterfile, replace

qui use deflators_n_ur_Q_2018, clear
sort yrq
save usefile, replace

qui use masterfile, clear
merge m:1 yrq using usefile, update replace
sort _merge
drop if _merge<3
drop _merge

//Match with HP
sort yrq
save masterfile, replace

qui use hp_Q_2018, clear
sort yrq
save usefile, replace

qui use masterfile, clear
merge m:1 yrq using usefile, update replace
sort _merge
drop if _merge<3
drop _merge

save GU04_18_Q_j_all, replace

//generate variables
qui replace yrq2=yq(year, quarter) //time variable must contain only integer values
qui tsset idj yrq2, quarterly 
qui replace logrgdp=log(realgdp) //from Q1 GDP 
qui replace logemply=log(j_employment)
qui replace logemplydiff=log(j_employment/l.j_employment)

egen thrsh1size = pctile(j_employment), p(33) //3
egen thrsh2size = pctile(j_employment), p(67) //6
qui replace size_big=1 if j_employment>=thrsh2size & ~missing(j_employment)
qui replace size_big=0 if j_employment<thrsh2size & ~missing(j_employment)
qui replace size_mid=1 if j_employment<thrsh2size & j_employment>=thrsh1size & ~missing(j_employment)
qui replace size_mid=0 if (j_employment>=thrsh2size | j_employment<thrsh1size) & ~missing(j_employment)
qui replace size_small=1 if j_employment<thrsh1size & ~missing(j_employment)
qui replace size_small=0 if j_employment>=thrsh1size & ~missing(j_employment)

qui replace rg_ne=1 if j_bls_state_code2<24 & ~missing(j_bls_state_code2)
qui replace rg_ne=0 if j_bls_state_code2>=24 & ~missing(j_bls_state_code2)
qui replace rg_mw=1 if j_bls_state_code2>24 & j_bls_state_code2<50 & ~missing(j_bls_state_code2)
qui replace rg_mw=0 if (j_bls_state_code2<=24 | j_bls_state_code2>=50) & ~missing(j_bls_state_code2)
qui replace rg_s=1 if j_bls_state_code2>50 & j_bls_state_code2<75 & ~missing(j_bls_state_code2)
qui replace rg_s=0 if (j_bls_state_code2<=50 | j_bls_state_code2>=75) & ~missing(j_bls_state_code2)
qui replace rg_w=1 if j_bls_state_code2>75 & j_bls_state_code2<100 & ~missing(j_bls_state_code2)
qui replace rg_w=0 if (j_bls_state_code2<=75 | j_bls_state_code2>100) & ~missing(j_bls_state_code2)

egen thrsh1 = pctile(j_ivf_grade_level), p(33) //3
egen thrsh2 = pctile(j_ivf_grade_level), p(67) //6
qui gen ivf_l=1 if j_ivf_grade_level<=thrsh1 & ~missing(j_ivf_grade_level)
qui replace ivf_l=0 if j_ivf_grade_level>thrsh1 & ~missing(j_ivf_grade_level)
qui gen ivf_m=1 if (j_ivf_grade_level<=thrsh2 & j_ivf_grade_level>thrsh1) & ~missing(j_ivf_grade_level)
qui replace ivf_m=0 if (j_ivf_grade_level>thrsh2 | j_ivf_grade_level<=thrsh1) & ~missing(j_ivf_grade_level)
qui gen ivf_h=1 if j_ivf_grade_level>thrsh2 & ~missing(j_ivf_grade_level)
qui replace ivf_h=0 if j_ivf_grade_level<=thrsh2 & ~missing(j_ivf_grade_level)

drop n1_*
qui tab n1, gen(n1_)

drop femog_*
qui tab j_mognum, gen(femog_)

save GU04_18_Q_j_all, replace // 


//===========================Calculate benefit groups================================================

//Hourly: (i) Basic earnings (ii) Total earnings (basic earnings plus overtime, bonuses etc.)  (iii) total compensation (total earnings + all benefits other than those already included in total earnings measure)
//D. (iii) broken down by benefit categories (quasi-fixed vs. other benefits; legally required vs. discretionary benefits that can be adjusted)

qui replace j_2=j_ahr+j_CQBENC01+j_CQBENC06+j_CQBENC07 // ahr + hourly premium pay, shift differentials, nonprod bonus 
qui replace j_comp=j_ahr+j_cqtotben //hourly total compensation already exists
qui replace j_qfbf=j_CQBENC02+j_CQBENC03+j_CQBENC04+j_CQBENC05+j_CQBENC10+j_CQBENC11+j_CQBENC13+j_CQBENC19+j_CQBENC20 //already exists: quasi-fixed benefit = vacations + holidays + sick leave + other paid leave + life/health insurances + defined bf + fed/state unemployment 
qui replace j_nonqf=j_cqtotben-j_qfbf //total benefit - quasi-fixed benefit
qui replace j_legal=j_CQBENC15+j_CQBENC16+j_CQBENC21+j_CQBENC19+j_CQBENC20+j_CQBENC22 //legally required = social security + medicare + workers compensation + fed unemployment + state unemployment + other legally 
qui replace j_disc=j_cqtotben-j_legal //discretionary benefits that can be adjusted 
qui replace j_other=j_CQBENC08+j_CQBENC09+j_CQBENC12+j_CQBENC17+j_CQBENC18+j_CQBENC23 //group of minor benefits = severance pay, supplemental unemployment, railroad supplementary retirement, railroad unemployment, short term and long term disability. 


//Quarterly total: (i) Basic earnings (ii) Total earnings (basic earnings plus overtime, bonuses etc.)  (iii) total compensation (total earnings + all benefits other than those already included in total earnings measure)
//D. (iii) broken down by benefit categories (quasi-fixed vs. other benefits; legally required vs. discretionary benefits that can be adjusted)

//from hourly number to annual number
foreach a of varlist j_CQBENC* j_cqtotben j_ahr {
qui replace a`a'=`a'*j_HOURS_WORKED 
}

qui replace aj_2=aj_ahr+aj_CQBENC01+aj_CQBENC06+aj_CQBENC07 // hours worked x (ahr + hourly premium pay, shift differentials, nonproduction bonus) 
qui replace aj_comp=aj_ahr+aj_cqtotben //already exists: Total annual compensation = hours worked x (ahr that is only for average straight time wage + overtime premium + all other benefits including paid leaves): overtime variable includes only premium part of overtime payment
qui replace aj_qfbf=aj_CQBENC02+aj_CQBENC03+aj_CQBENC04+aj_CQBENC05+aj_CQBENC10+aj_CQBENC11+aj_CQBENC13+aj_CQBENC19+aj_CQBENC20 //already exists: quasi-fixed benefit = vacations + holidays + sick leave + other paid leave + life/health insurances + defined bf + fed/state unemployment 
qui replace aj_nonqf=aj_cqtotben-aj_qfbf //total benefit - quasi-fixed benefit
qui replace aj_legal=aj_CQBENC15+aj_CQBENC16+aj_CQBENC21+aj_CQBENC19+aj_CQBENC20+aj_CQBENC22 //legally required = social security + medicare + workers compensation + fed unemployment + state unemployment + other legally
qui replace aj_disc=aj_cqtotben-aj_legal //discretionary benefits that can be adjusted 
qui replace aj_other=aj_CQBENC08+aj_CQBENC09+aj_CQBENC12+aj_CQBENC17+aj_CQBENC18+aj_CQBENC23 //group of minor benefits = severance pay, supplemental unemployment, railroad supplementary retirement, railroad unemployment, short term and long term disability. 

//Make them real:
qui tsset idj yrq2, quarterly 
sort idj yrq2

//CPI all/GNP df
foreach a of varlist j_2 j_nonqf j_legal j_disc j_other aj_2 aj_nonqf aj_legal aj_disc aj_other j_comp j_qfbf j_CQBENC* j_cqtotben j_ahr aj_comp aj_qfbf aj_CQBENC* aj_cqtotben aj_ahr { 
qui replace log`a'=log(`a'/cpi)
qui replace logdiff`a'=log`a'-l.log`a'

qui replace loggnpdf`a'=log(`a'/gnpdf) 
qui replace logdiffgnpdf`a'=loggnpdf`a'-l.loggnpdf`a'
}

//Robustness: cpi by region
foreach a of varlist j_2 j_nonqf j_legal j_disc j_other aj_2 aj_nonqf aj_legal aj_disc aj_other j_comp j_qfbf j_CQBENC* j_cqtotben j_ahr aj_comp aj_qfbf aj_CQBENC* aj_cqtotben aj_ahr { 
qui replace logcrg`a'=0
qui replace logdiffcrg`a'=0

qui replace logcrg`a'=log(`a'/cpine) if rg_ne==1
qui replace logdiffcrg`a'=logcrg`a'-l.logcrg`a' if rg_ne==1

qui replace logcrg`a'=log(`a'/cpimw) if rg_mw==1
qui replace logdiffcrg`a'=logcrg`a'-l.logcrg`a' if rg_mw==1

qui replace logcrg`a'=log(`a'/cpiw) if rg_w==1
qui replace logdiffcrg`a'=logcrg`a'-l.logcrg`a' if rg_w==1

qui replace logcrg`a'=log(`a'/cpis) if rg_s==1
qui replace logdiffcrg`a'=logcrg`a'-l.logcrg`a' if rg_s==1
}


//CPI medical care
foreach a of varlist j_CQBENC11 aj_CQBENC11{ 
qui replace logcmed`a'=log(`a'/cpimed)
qui replace logdiffcmed`a'=logcmed`a'-l.logcmed`a'
}

//Robustness: ppi
foreach a of varlist j_2 j_nonqf j_legal j_disc j_other aj_2 aj_nonqf aj_legal aj_disc aj_other j_comp j_qfbf j_CQBENC* j_cqtotben j_ahr aj_comp aj_qfbf aj_CQBENC* aj_cqtotben aj_ahr { 
qui replace logppi`a'=log(`a'/ppi)
qui replace logdiffppi`a'=logppi`a'-l.logppi`a'
}

//Robustness: ppi manufacturing
foreach a of varlist j_2 j_nonqf j_legal j_disc j_other aj_2 aj_nonqf aj_legal aj_disc aj_other j_comp j_qfbf j_CQBENC* j_cqtotben j_ahr aj_comp aj_qfbf aj_CQBENC* aj_cqtotben aj_ahr { 
qui replace logppim`a'=0
qui replace logdiffppim`a'=0

qui replace logppim`a'=log(`a'/ppim) if n1==3
qui replace logdiffppim`a'=logppim`a'-l.logppim`a' if n1==3
}

//regenerate id
qui replace id=int(idj/10)

//no error for: panels are not nested within clusters
qui egen aaa=sd(n3), by(idj)
drop if aaa>0
summarize aaa
drop aaa

save GU04_18_Q_j_all, replace
log close
